package com.jomoo.common.ajax.dao.impl;

import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.jomoo.common.ajax.dao.JqueryDao;

/**
 * 
* @ClassName: JqueryDaoImpl 
* @Description: 
* @author sddm.haosc
* @date 2017年12月12日 上午10:33:01 
*
 */
@Repository
@Transactional(rollbackFor = Exception.class)
public class JqueryDaoImpl implements JqueryDao
{

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	/**
	 * 获取所有产品分类
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getProducts(Integer iCustomerId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List proList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append("from JoMooType jt where jt.active=1 and jt.customerId=:ccid ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("ccid", iCustomerId);

			proList = query.list();
			
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return proList;
	}

	/**
	 * 获取所有产品分类规格
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getProductSpec(Integer iProId) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List proList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append("from JoMooSpec js where js.active=1 and js.typeId=:proid ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("proid", iProId);

			proList = query.list();
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return proList;
	}

	/**
	 * 获取所有一级城市(地址调用)
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getFirstCity() throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List fcityList = null;

		try
		{

			StringBuilder hql = new StringBuilder();
			
			hql.append(" select new JoMooCity(jc.id, jc.cityName) from JoMooCity jc where jc.active=1 and jc.isTop=1");

			Query query = session.createQuery(hql.toString());

			fcityList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return fcityList;
	}

	/**
	 * 获取所有二级城市(地址调用)
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getSecondCity(Integer iSecCityid) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List scityList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select new JoMooCity(jc.cityName) from JoMooCity jc where jc.active=1 and jc.isTop=0 and jc.parentId=:scityid ");

			Query query = session.createQuery(hql.toString());

			query.setInteger("scityid", iSecCityid);

			scityList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return scityList;
	}

	/**
	 * 获取所有客户
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getCustomers() throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List scityList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" select new JoMooCustomer(jc.customerType, jc.customerName) "
					+ " from JoMooCustomer jc where jc.active=1");

			Query query = session.createQuery(hql.toString());

			scityList = query.list();
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return scityList;
	}

	/**
	 * 获取所有安装类型
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getSetupType() throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List repairList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" select new JoMooRepair(jr.id, jr.repairName) "
					+ " from JoMooRepair jr");

			Query query = session.createQuery(hql.toString());

			repairList = query.list();
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return repairList;
	}

	/**
	 * 获取所有状态详情
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getAllStates(String strStateName, String strAllFlag) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List stateList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select new JoMooStatus(js.statusValue, js.statusName) "
					+ " from JoMooStatus js where js.active=1 and js.stateName=:name ");
			
			if("ACCOUNTTYPE".equals(strStateName) && (StringUtils.isBlank(strAllFlag)))
			{
				hql.append(" and js.statusValue != 0");
			}

			Query query = session.createQuery(hql.toString());

			query.setString("name", strStateName);

			stateList = query.list();
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return stateList;
	}

	/**
	 * 根据不同标识查询不同情况数量用于页面显示
	 * 
	 * @param strFlag
	 * @param date
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer getScount(String strFlag, Date strStartDate, Date strEndDate) throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = 0;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" select count(*) from JoMooCard jc where jc.active=1 ");

			if ("wait".equals(strFlag))
			{
				hql.append(" and jc.setupType=1 and jc.setupState=2 and jc.setupTime>=:starttime and jc.setupTime<:endtime ");
			}

			if ("fix".equals(strFlag))
			{
				hql.append(" and jc.setupType=2 and jc.setupState=2 and jc.setupTime>=:starttime and jc.setupTime<:endtime ");
			}

			if ("exception".equals(strFlag))
			{
				hql.append(" and jc.setupState=0 ");
			}

			if ("yanchi".equals(strFlag))
			{
				hql.append(" and jc.isLazy=1 and jc.setupState=3 and jc.lazyTime>=:starttime ");
			}

			Query query = session.createQuery(hql.toString());

			if ("wait".equals(strFlag) || "fix".equals(strFlag))
			{
				query.setDate("starttime", strStartDate);
				query.setDate("endtime", strEndDate);
			}

			if ("yanchi".equals(strFlag))
			{
				query.setDate("starttime", strStartDate);
			}

			Number num = (Number) query.uniqueResult();

			iCount = num.intValue();

		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}

	/**
	 * 获取条数决定显示通知内容
	 * 
	 * @param strStartDate
	 * @param strEndDate
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer getWaitCountForShow(Date strStartDate, Date strEndDate)
			throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = 0;

		try
		{
			StringBuilder hql = new StringBuilder();
			hql.append(" select count(*) from JoMooCard jc where jc.active=1 "
					+ " and jc.setupTime>=:starttime and jc.setupTime<:endtime "
					+ " and jc.setupType=1 and jc.setupState=2 ");

			Query query = session.createQuery(hql.toString());

			query.setDate("starttime", strStartDate);
			query.setDate("endtime", strEndDate);

			Number num = (Number) query.uniqueResult();

			iCount = num.intValue();

		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}

	/**
	 * 获取条数决定显示通知内容
	 * 
	 * @param strStartDate
	 * @param strEndDate
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer getFixCountForShow(Date strStartDate, Date strEndDate)
			throws Exception
	{
		Session session = sessionFactory.openSession();
		
		Integer iCount = 0;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select count(*) from JoMooCard jc where jc.active=1 and jc.setupType=2 and jc.setupState=2 "
					+ " and jc.setupTime>=:starttime and jc.setupTime<:endtime");

			Query query = session.createQuery(hql.toString());

			query.setDate("starttime", strStartDate);
			query.setDate("endtime", strEndDate);

			Number num = (Number) query.uniqueResult();

			iCount = num.intValue();

		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}

	/**
	 * 页面点击条数查询详情
	 * 
	 * @param iFlag
	 * @param strDate
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getScountViews(Integer iFlag, Date strStartDate, Date strEndDate)
			throws Exception
	{
		Session session = sessionFactory.openSession();
		
		List cardList = null;

		try
		{
			StringBuilder hql = new StringBuilder();

			if (1 == iFlag || 2 == iFlag || 5 == iFlag)
			{
				hql.append(" select new JoMooCard(jc.id, jc.popName, jt.typeName, js.specName, jc.popPhone, jc.setupTime, jc.popAddress) ");
			}

			if (4 == iFlag)
			{
				hql.append(" select new JoMooCard(jc.id, jc.popName, jt.typeName, js.specName, jc.popPhone, jc.setupTime, jc.lazyTime, jc.popAddress) ");
			}

			if (3 == iFlag)
			{
				hql.append(" select new JoMooCard(jc.id, jc.popName, jt.typeName, js.specName, jc.popPhone, jc.setupTime, jc.popAddress,jc.remark) ");
			}

			hql.append(" from JoMooCard jc, JoMooType jt, JoMooSpec js where "
					+ " jt.active=1 AND jc.typeId=jt.id AND js.active=1 AND js.id=jc.specId ");

			if (1 == iFlag)
			{
				hql.append(" and jc.active=1 AND jc.setupType=1 AND jc.setupState=2 and jc.setupTime>=:starttime and jc.setupTime<:endtime ");
			}

			if (2 == iFlag)
			{
				hql.append(" and jc.active=1 AND jc.setupType=2 AND jc.setupState=2 and jc.setupTime>=:starttime and jc.setupTime<:endtime ");
			}

			if (4 == iFlag)
			{
				hql.append(" and jc.active=1 AND jc.isLazy=1 AND jc.setupState=3 and jc.lazyTime>=:starttime ");
			}

			if (3 == iFlag)
			{
				hql.append(" and jc.active=1 AND jc.setupState=0 ");
			}

			if (5 == iFlag)
			{
				hql.append(" and jc.active=0 ");
			}

			Query query = session.createQuery(hql.toString());

			if (1 == iFlag || 2 == iFlag)
			{
				query.setDate("starttime", strStartDate);
				query.setDate("endtime", strEndDate);
			}

			if (4 == iFlag)
			{
				query.setDate("starttime", strStartDate);
			}

			cardList = query.list();

		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return cardList;
	}
}